/*
 * Database.java
 *
 * Created on 30 de Agosto de 2007, 21:10
 * @author Evolution Systems - Do the Evolution
 */


package bd;

import classes.LogErro;
import java.sql.*;
	
public class Database
{
    final public String POSTGRESQL= "org.postgresql.Driver";
    final public String MYSQL     = "org.gjt.mm.mysql.Driver";
    final public String MSSQL     = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
    final public String INTERBASE = "org.firebirdsql.jdbc.FBDriver";
    final public String ACCESS    = "sun.jdbc.odbc.JdbcOdbcDriver";

    final public String CLASS_NOT_FOUND    = "Classe de conex�o não encontrada! ";
    final public String SQL_EXCEPTION      = "Excessão SQL! ";

    public Settings settings = new Settings();
    private boolean Connected = false;

    final public boolean doUseTransaction = true;
    final public boolean doNotUseTransaction = false;
    public boolean Error;
    public String  ErrorMessage;


    private Connection cn;
    private Statement  stm;

    public boolean isConnected()
    {
        return Connected;
    }

    public boolean connect()
    {
        String url="", protocol="";	
        boolean result= false;

        protocol= settings.getDatabaseProtocol();
        /*Postgres*/
        if( protocol.equals(POSTGRESQL))
        {
                url = "jdbc:postgresql://"
                                +settings.getDatabaseHost()
                                                         +"/"+settings.getDatabaseName()
                                                         +"?user="+settings.getDatabaseUser()
                                                         +"&password="+settings.getDatabasePassword();
        }
        /*Mysql*/
        else if( protocol.equals(MYSQL))
        {
                url = "jdbc:mysql://"
                            +settings.getDatabaseHost()
                                                    +"/"+settings.getDatabaseName()
                                                    +"?user="+settings.getDatabaseUser()
                                                    +"&password="+settings.getDatabasePassword();
        }
        /*Microsoft SQL SERVER EXPRESS*/
        else if( protocol.equals(MSSQL))
        {
                url = "jdbc:microsoft:sqlserver://" 
                            +settings.getDatabaseHost() 
                            +"/"+settings.getDatabaseName()
                            +"?user="+settings.getDatabaseUser()
                            +"&password="+settings.getDatabasePassword();

        }	
        /*Interbase/Firebird*/		
        else if( protocol.equals(INTERBASE))
        {
                url =  "jdbc:firebirdsql:"
                        +settings.getDatabaseHost()
                        +"/3050:" 
                        +"/"+settings.getDatabaseName()
                        +"?user="+settings.getDatabaseUser()
                        +"&password="+settings.getDatabasePassword();
        }		
        /*Microsoft ACCESS heheheheheeh (dos b�os)*/
        else if( protocol.equals(ACCESS))
        {
                url = "jdbc:odbc:"+settings.getDatabaseName();
        }		
        else
        {
                ErrorMessage= "Protocolo não especificado";
                return false;
        }

        try
        {
                Class.forName(protocol);	
                if( protocol == ACCESS)
                        cn= DriverManager.getConnection(url,settings.getDatabaseUser(),settings.getDatabasePassword());
                else
                        cn = DriverManager.getConnection(url);
                stm = cn.createStatement(ResultSet.CONCUR_READ_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE); 
                result = true;;
        }
        catch(ClassNotFoundException e)
        {
                ErrorMessage= CLASS_NOT_FOUND+" : "+e;
                result = false;
        }
        catch(SQLException se)
        {
                ErrorMessage= SQL_EXCEPTION+" :"+se;
                result = false;
        }
        Connected = result;
        return result;
    }

    public void disconnect()
    {
        try
        {
                if(Connected)
                        cn.close();
                Error= false;
                Connected = false;
        }
        catch(SQLException e)
        {
                Error= true;
                ErrorMessage= SQL_EXCEPTION+" :"+e;
        }
    }
	
    public void executeCommand(String sql, boolean autoCommit)
    {
        try
        {
            if( autoCommit)
            {
                cn.setAutoCommit(false);
                cn.setTransactionIsolation(cn.TRANSACTION_READ_COMMITTED);
            }
        }
        catch(SQLException se){
            Error= true;
            ErrorMessage = SQL_EXCEPTION+" :"+se;       
            LogErro.writeError(ErrorMessage+"\n"+sql);
        } 
        
        try
        {
            stm.executeUpdate(sql);
            Error= false;                
        }
        catch(SQLException e)
        {
            if( autoCommit )
               rollbackTransaction();             
            Error= true;
            ErrorMessage = SQL_EXCEPTION+" :"+e;
            
            LogErro.writeError(ErrorMessage+"\n"+sql);
        }
        finally
        {
             if (autoCommit)
                        commitTransaction();
        }
    }
	
    public void beginTransaction()
    {
            try{
                cn.setAutoCommit(false);
                cn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
            }
            catch(SQLException se){
                Error= true;
                ErrorMessage = SQL_EXCEPTION+" :"+se;                    
            }     
    }
        
    public void commitTransaction() 
    {
        try{
            cn.commit();
            Error = false;
        }                 
        catch (SQLException se){
            ErrorMessage = SQL_EXCEPTION + " : "+se;
            Error = true;
        }
    }
    
    public void rollbackTransaction()
    {
        try 
        {
            cn.rollback();
            Error = false;
        }
        catch (SQLException se)
        {
            ErrorMessage = SQL_EXCEPTION + " : "+se;
            Error = true;
        }
    }    
   
    public DataSet executeQuery(String sql) 
    {
        if(!Connected){
            Error= true;
            System.out.println("Banco de dados não conectado!");
            ErrorMessage= "Banco de dados não conectado!";
            return null;
        }
        System.out.println("Executou!");
        DataSet ds = new DataSet();
        try
        {
            ResultSet rs = stm.executeQuery(sql);
            ds.fill(rs);
            Error= false;
            return ds;
        }
        catch(SQLException e)
        {
            Error= true;
            ErrorMessage = SQL_EXCEPTION+" :"+e;
            LogErro.writeError(ErrorMessage+"\n"+sql);
        }
        return null;
    }
 
    public ResultSet executeQueryRs(String sql) 
    {
        try
        {
            ResultSet rs = stm.executeQuery(sql);
            Error= false;
            return rs;
        }
        catch(SQLException e)
        {
            Error= true;
            ErrorMessage = SQL_EXCEPTION+" :"+e;
            LogErro.writeError(ErrorMessage+"\n"+sql);            
        }
        return null;
    }
  
    public Object executeScalar(String sql) 
    {
        try
        {
            ResultSet rs = stm.executeQuery(sql);
            Error= false;
            if( rs.next())
                return rs.getObject(1);
            return null;
        }
        catch(SQLException e)
        {
            Error= true;
            ErrorMessage = SQL_EXCEPTION+" :"+e;
            LogErro.writeError(ErrorMessage+"\n"+sql);          
            return null;            
        }
    }
    
    public Connection getConnection()
    {
        return cn;
    }
}